import openpyxl

wb = openpyxl.load_workbook('baidu.xlsx')
excel_sheet = wb.active

#*******************************人数
print(f"表格中有{excel_sheet.max_row-1}人")

#******************************用户占比
yd = [134,135,136,137,138,139,147,
        150,151,152,157,158,159,178,
        182,183,184,187,188,
        1703,1705,1706]
lt = [130,131,132,145,155,156,
        175,176,185,186,171,
        1704,1707,1708,1709]
dx = [133,149,153,173,177,
        180,181,189,
        1700,1701,1702]
yd_a =len(yd)
lt_a =len(lt)
dx_a =len(dx)
count_yd = 0
count_lt = 0
count_dx = 0
sheet = wb['Sheet1']
columns = sheet['F']
i =1#去掉检验
for i in range(1,len(columns)):
    cell = columns[i]
    a = int(cell.value[:3])
    b = int(cell.value[:4])
    i+=1
    if a != 170:
        for j in range(yd_a):
            if a == yd[j]:
                count_yd += 1
        for k in range(lt_a):
            if a == lt[k]:
                count_lt += 1
        for l in range(dx_a):
            if a == dx[l]:
                count_dx += 1
    else:
        for m in range(yd_a):
            if b == yd[m]:
                count_yd += 1
            if b == lt[m]:
                count_lt += 1
            if b == dx[m]:
                count_dx += 1
yd_percent = count_yd/(excel_sheet.max_row-1)*100
lt_percent = count_lt/(excel_sheet.max_row-1)*100
dx_percent = count_dx/(excel_sheet.max_row-1)*100
print(f"移动有{count_yd}人，占比:{yd_percent:.2f}%\n"
      f"联通有{count_lt}人，占比:{lt_percent:.2f}%\n"
      f"电信有{count_dx}人，占比:{dx_percent:.2f}%")

#******************************男女人数
sheet1 = wb['Sheet1']
columns1 = sheet1['I']
count_nan = 0
count_nv = 0
for i in range(1,len(columns1)):
    cell = columns1[i]
    sex = cell.value
    if sex == '男':
        count_nan +=1
    else:
        count_nv +=1
print(f"男有{count_nan}人，女有{count_nv}人")

#***************************超过45岁的人数
sheet2 = wb['Sheet1']
columns2 = sheet2['H']
count_m45 = 0
for i in range(1,len(columns2)):
    cell = columns2[i]
    age = cell.value
    if age > 45:
        count_m45 +=1
print(f"超过45岁的人数有{count_m45}人")

#******************薪资超过8000，小于3000的人
sheet3 = wb['Sheet1']
columns3 = sheet3['L']
count_m8000 = 0
count_l3000 = 0
for i in range(1,len(columns3)):
    cell = columns3[i]
    money = cell.value
    if money > 8000:
        count_m8000 +=1
    if money < 3000:
        count_l3000 +=1
print(f"薪资超过8000的人数有{count_m8000}人，薪资小于3000的人数有{count_l3000}人")

#*********************传媒公司
sheet4 = wb['Sheet1']
columns4 = sheet4['N']
count_media = 0
for i in range(1,len(columns4)):
    cell = columns4[i]
    company = cell.value
    if "传媒" in company:
        count_media +=1
print(f"传媒公司的人数有{count_media}人")

#*************************疫情高危地区
sheet5 = wb['Sheet1']
columns5 = sheet5['J']
count_address = 0
for i in range(1,len(columns5)):
    cell = columns5[i]
    address = cell.value
    if "黑龙江" in address:
        count_address +=1
    if "北京" in address:
        count_address +=1
    if "福建" in address:
        count_address +=1
    if "四川" in address:
        count_address +=1
print(f"疫情高危地区的人数有{count_address}人")

